package com.yingdong.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.yingdong.db.domain.DBObjects;
import com.yingdong.db.utils.DBManager;
import com.yingdong.db.utils.DBParamWrapper;

public abstract class BaseDao<T extends DBObjects> {

	static Logger logger = LoggerFactory.getLogger(BaseDao.class);

	public T queryOne(String sql, DBParamWrapper dbParamWrapper) throws SQLException {

		PreparedStatement st = null;
		ResultSet rs = null;
		Connection connection = null;
		try {
			connection = DBManager.getConnection();
			st = connection.prepareStatement(sql);
			DBManager.prepareStatementSet(st, dbParamWrapper);
			rs = st.executeQuery();
			while (rs.next()) {
				return resultSetToObject(rs);
			}
		} catch (SQLException e) {
			throw e;
		} finally {
			DBManager.closeConnection(connection, st, rs);
		}
		return null;

	}

	public List<T> queryList(String sql) throws SQLException {
		return queryList(sql, null);

	}

	public List<T> queryList(String sql, DBParamWrapper dbParamWrapper) throws SQLException {
		PreparedStatement st = null;
		ResultSet rs = null;
		Connection connection = null;
		List<T> list = new ArrayList<>();
		try {
			connection = DBManager.getConnection();
			st = connection.prepareStatement(sql);
			DBManager.prepareStatementSet(st, dbParamWrapper);
			rs = st.executeQuery();
			while (rs.next()) {
				list.add(resultSetToObject(rs));
			}
			return list;
		} catch (SQLException e) {
			throw e;
		} finally {
			DBManager.closeConnection(connection, st, rs);
		}

	}

	public int excuteSql(String sql) throws SQLException {
		return excuteSql(sql, null);
	}

	public int excuteSql(String sql, DBParamWrapper dbParamWrapper) throws SQLException {
		PreparedStatement st = null;
		Connection connection = null;
		try {
			connection = DBManager.getConnection();
			connection.setAutoCommit(false);
			st = connection.prepareStatement(sql);
			DBManager.prepareStatementSet(st, dbParamWrapper);
			int idx = st.executeUpdate(sql);
			connection.commit();
			return idx;
		} catch (SQLException e) {
			connection.rollback();
			throw e;
		} finally {
			DBManager.closeConnection(connection, st);
		}
	}

	public int[] excuteSqlBatch(String sql, List<DBParamWrapper> dbParamWrappers) throws SQLException {
		PreparedStatement st = null;
		Connection connection = null;
		try {
			connection = DBManager.getConnection();
			connection.setAutoCommit(false);
			st = connection.prepareStatement(sql);
			DBManager.prepareStatementBatchSet(st, dbParamWrappers);
			int[] idx = st.executeBatch();
			connection.commit();
			return idx;
		} catch (SQLException e) {
			connection.rollback();
			throw e;
		} finally {
			DBManager.closeConnection(connection, st);
		}
	}

	public List<Integer> excuteIntegerInsertBatch(String sql, List<DBParamWrapper> dbParamWrappers) throws SQLException {
		PreparedStatement st = null;
		ResultSet rs = null;
		Connection connection = null;

		try {
			connection = DBManager.getConnection();
			connection.setAutoCommit(false);
			st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			DBManager.prepareStatementBatchSet(st, dbParamWrappers);
			st.executeBatch();
			connection.commit();
			rs = st.getGeneratedKeys();
			List<Integer> list = new ArrayList<>();
			while (rs.next()) {
				list.add(rs.getInt(1));
			}
			return list;
		} catch (SQLException e) {
			connection.rollback();
			throw e;
		} finally {
			DBManager.closeConnection(connection, st, rs);
		}

	}

	public List<Long> excuteLongInsertBatch(String sql, List<DBParamWrapper> dbParamWrappers) throws SQLException {
		PreparedStatement st = null;
		ResultSet rs = null;
		Connection connection = null;

		try {
			connection = DBManager.getConnection();
			connection.setAutoCommit(false);
			st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			DBManager.prepareStatementBatchSet(st, dbParamWrappers);
			st.executeBatch();
			connection.commit();
			rs = st.getGeneratedKeys();
			List<Long> list = new ArrayList<>();
			while (rs.next()) {
				list.add(rs.getLong(1));
			}
			return list;
		} catch (SQLException e) {
			connection.rollback();
			throw e;
		} finally {
			DBManager.closeConnection(connection, st, rs);
		}

	}

	public abstract T resultSetToObject(ResultSet resultSet) throws SQLException;

	public abstract DBParamWrapper domainToWrapper(T t);
}
